from django.shortcuts import render
from datetime import *
from django.db.models import *

from .models import *
# Create your views here.
def index(request):
    books = BookInfo.books.all()
    test1 = BookInfo.books.get(pk=1)
    herolist = test1.heroinfo_set.all()
    herovalude = herolist.values()

    count = BookInfo.books.count()
    first = BookInfo.books.first()
    last = BookInfo.books.last()
    exact = BookInfo.books.filter(isDelete=False)
    contain = BookInfo.books.filter(btitle__contains='飞')
    exclude = BookInfo.books.exclude(btitle__contains='飞')
    endswith = BookInfo.books.exclude(btitle__endswith='传')
    startswith = BookInfo.books.exclude(btitle__startswith='传')
    isnull = BookInfo.books.filter(btitle__isnull=False)
    s_in = BookInfo.books.filter(pk__in=[1,2])
    #gt、gte、lt、lte
    gt = BookInfo.books.filter(pk__gt=2)
    year = BookInfo.books.filter(bpub_date__year=1980)
    dategt = BookInfo.books.filter(bpub_date__gt=date(1980, 12, 31))
    #跨表查询
    twotable = BookInfo.books.filter(heroinfo__hcontent__contains = '八')
    #聚合函数Avg，Count，Max，Min，Sum
    maxDate = twotable.aggregate(Max('bpub_date'))
    aggr_count = twotable.aggregate(Count('btitle'))
    #F对象
    f1 = BookInfo.books.filter(bread__gte=F('bcommet'))
    f2 = BookInfo.books.filter(bread__gte=F('bcommet') * 2)
    f3 = BookInfo.books.filter(isDelete=F('heroinfo__isDelete'))
    f4 = BookInfo.books.filter(bpub_date__lt=F('bpub_date') + timedelta(days=1))
    #Q对象
    q1 = BookInfo.books.filter(Q(pk__lt = 6))
    q2 = BookInfo.books.filter(Q(pk__lt = 6) | Q(bcommet__gt = 10))
    q3 = BookInfo.books.filter(~Q(pk__lt=3))

    #自连接
    # class AreaInfo(models.Model):
    #     atitle = models.CharField(max_length=20)
    #     aParent = models.ForeignKey('self', null=True, blank=True)
    # 上级对象：area.aParent
    # 下级对象：area.areainfo_set.all()
    area = AreaInfo.objects.get(pk=130100)
    # < ul >
    # { %
    # for a in area.areainfo_set.all %}
    # < li > {{a.atitle}} < / li >
    # { % endfor %}
    # < / ul >
    context ={
        'books':books,
        'test1':test1,
        'herolist':herolist,
        'herovalude':herovalude,
        'count':count,
        'first':first,
        'last':last,
        'exact':exact,
        'contain':contain,
        'exclude':exclude,
        'endswith':endswith,
        'startswith': startswith,
        'isnull':isnull,
        's_in':s_in,
        'gt':gt,
        'year':year,
        'dategt':dategt,
        'twotable':twotable,
        'maxdate':maxDate,
        'aggr_count':aggr_count,
        'f1':f1,
        'f2':f2,
        'f3':f3,
        'f4':f4,
        'q1':q1,
        'q2':q2,
        'q3':q3,

    }
    return render(request,'booktest/index.html',context)